
package com.ce.cases.collegial.logic.search;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

import com.ce.cases.collegial.view.CaseSourceCollegialView;
import com.ce.framework.datamaintain.action.SearchAction;
import com.ce.framework.datamaintain.logic.ISQLMapper;
import com.ce.framework.datamaintain.logic.SQLSearchCondition;
import com.ce.framework.exception.AppException;
import com.ce.framework.util.DateUtil;
import com.ce.framework.util.StringUtil;
import com.ce.sys.login.model.User;
import com.ce.sys.org.dao.po.SysVOrg;
import com.ce.util.OrgUtil;

/**
* 嫦娥软件有限公司 版权所有 2013
* 创建人：　andy
* 创建日期：2014-04-11
* 创建时间：10:00:00
* 功能描述：市局合议台账 [CASE_SOURCE_COLLEGIAL]条件过滤类
* ==============================================
* 修改历史
* 修改人		修改时间		修改原因
*
* ==============================================
*/
public class CaseSourceCollegialSearchCondition  extends SQLSearchCondition
	implements ISQLMapper
{
	private String taxpayerNum;
    private String involvedEnt;
    private String caseNum;
    private String recBeginDate;
    private String recEndDate;
    private String caseFrom;
    private String illegalType;
    private String inspectDept;
    private String collectDept;
    
    private String curOrgId;
    
    private String curOrgLevel;
    private String curOrgType;
    
    private String curRootOrgId;
    private String curPersonId;
    private String curRootOrgLevel;
    private String curPersonPosition;//当前人职位
    @Override
	public String getExcelName() {
		return "市局合议台账_"+DateUtil.format(new Date(),"yyyyMMddHHmm");
	}
	@Override
	public void initCondition(SearchAction action, boolean search)
			throws AppException {
		
		User user=User.getUser();
		curRootOrgId=user.getRootOrgId();
		curRootOrgLevel=user.getRootOrgLevel();
		this.curOrgId=user.getVcOrgId();
		curOrgLevel=user.getOrgLevel();
		curPersonId=user.getUserId();
		curPersonPosition=user.getVcPosition();
	}
	public String getSearchSql() throws AppException {
		/* 2015-05-20 by 刘渝洪，开始 
		 * 把审理提交时间（case_source_collegial_draw.register_date）设置为合议提请时间（case_source_collegial.commit_date）
		 * 从而在市局合议台帐，搜索出的合议案件列表里，【合议提请时间】列显示为“审理提交时间”。
		 * StringBuffer sql = new StringBuffer("select t1.vc_id,t1.case_number,t1.involved_enterprice as enterprice,t3.commit_date,t3.end_date,t3.main_dispute,t3.punishment_deal")
		 * .append(" from case_source t1,T_WORKFLOW_WORK work,CASE_SOURCE_HEAR t2,case_source_collegial t3")
		 * .append(" where t1.vc_del$flag=0 and  t1.vc_id=t2.case_id and t1.vc_id=t3.case_id(+) and t2.IS_COLLEGIATE=1 and t1.vc_id=work.VC_RECORD_ID");
		 */
		String END_DATE_s = DateUtil.getYear() + "-01-01 00:00:00";
		String END_DATE_e = DateUtil.getYear() + "-12-31 23:59:59";

		StringBuffer sql = new StringBuffer("select t1.vc_id,t1.case_number,t1.involved_enterprice as enterprice,t4.register_date as commit_date,t3.end_date,t3.main_dispute,t3.punishment_deal")
		.append(" from case_source t1,T_WORKFLOW_WORK work,CASE_SOURCE_HEAR t2,case_source_collegial t3,case_source_collegial_draw t4")
		.append(" where t1.vc_del$flag=0 and  t1.vc_id=t2.case_id and t1.vc_id=t3.case_id(+) and t1.vc_id=t4.case_id(+) and t2.IS_COLLEGIATE=1 and t1.vc_id=work.VC_RECORD_ID"
				+" AND ((t3.end_date>=TO_DATE('"+END_DATE_s+"','yyyy-mm-dd hh24:mi:ss') AND t3.end_date<=TO_DATE('"+END_DATE_e+"','yyyy-mm-dd hh24:mi:ss'))or t3.commit_date IS NULL)"
				);

		/* 2016年5月10日10:38:57 周路，结束 */
		
		if("3".equals(this.curOrgLevel)){
			sql.append(" and t1.INSPECTION_DEPT_ID ='"+curRootOrgId+"' ");			
			if("08".equals(curPersonPosition)){//跨区局科长
				sql.append(" and (work.VC_HANDLE_ORGIDS like '%"+this.curOrgId+"%' or work.VC_HANDLE_ORGID='"+this.curOrgId+"')");
			}else if("10".equals(curPersonPosition)){//跨区局科员
				sql.append(" and (work.VC_HANDLE_PERSONIDS like '%"+this.curPersonId+"%' or work.VC_HANDLE_PERSON_ID='"+this.curPersonId+"')");
			}
			
		}else if("2".equals(this.curOrgLevel)){
			//sql.append(" and (t.STATE=3)");
		}else{
			sql.append(" and (work.VC_STATUS='21')");//其它人查看，，查询结果为空。VC_STATUS永远不对等于21  
		}
		
		//采用变量绑定的方式提高查询效率
		if(StringUtil.isNotBlank(taxpayerNum)){
			sql.append(" and t1.taxpayer_number like ${taxpayerNum}||'%'");
		}
		if(StringUtil.isNotBlank(involvedEnt)){
			sql.append(" and t1.involved_enterprice like '%'||${involvedEnt}||'%'");
		}
		if(StringUtil.isNotBlank(caseNum)){
			sql.append(" and t1.case_number=${caseNum}");
		}
		/*
		 * 2016年5月10日10:49:35 by 周路，开始
		 * 修改搜索处理
		 * 默认初始列表时，或搜索日期条件没值时，搜索出的结果为：全部未结案的数据，以及本年度的已结案数据
		 * 设置搜索日期条件时，搜索出的结果为：满足日期条件的全部数据
		 * 原代码：
		 * sql.append(this.getDateCondition("receive_date", recBeginDate, recEndDate));
		 * 修改后代码：
		 */
		if (StringUtil.isNotBlank(recBeginDate)
				|| StringUtil.isNotBlank(recEndDate)) {
			sql.append(this.getDateCondition("receive_date", recBeginDate, recEndDate));
		} else {
			sql.append(" and (to_number(work.vc_status)<>9 or (to_number(work.vc_status)=9 ");
			String dateBeginOfThisYear = DateUtil.getYear() + "-01-01 00:00:00";
			String dateEndOfThisYear = DateUtil.getYear() + "-12-31 23:59:59";
			sql.append(" and ").append("receive_date").append(">=to_date('")
					.append(dateBeginOfThisYear)
					.append("','yyyy-mm-dd hh24:mi:ss') ");
			sql.append(" and ").append("receive_date").append("<=to_date('")
					.append(dateEndOfThisYear)
					.append("','yyyy-mm-dd hh24:mi:ss') ");
			sql.append("))");
		}
		/* 结束 */
		if(StringUtil.isNotBlank(caseFrom)){
			sql.append(" and t1.case_from_num=${caseFrom}");
		}
		if(StringUtil.isNotBlank(illegalType)){
			sql.append(" and t1.illegal_type_num=${illegalType}");
		}
		if(StringUtil.isNotBlank(inspectDept)){
			sql.append(" and t1.inspection_dept_id=${inspectDept}");
		}
		if(StringUtil.isNotBlank(collectDept)){
			sql.append(" and t1.collection_dept_id=${collectDept}");
		}
		/*=========自行修改该处代码================
		//实现排序的代码
		sql.append(" "+super.getOrderBy());
		======================================*/
		return sql.toString();
	}
	
	public Object fromSQLQuery(ResultSet rs, int num) {
		CaseSourceCollegialView view = new CaseSourceCollegialView();
		try {
			view.setVcId(rs.getString("VC_ID"));
			view.setCaseNumber(rs.getString("CASE_NUMBER"));
			view.setEnterprice(rs.getString("ENTERPRICE"));
			view.setCommitDate(rs.getDate("COMMIT_DATE"));
			view.setEndDate(rs.getDate("END_DATE"));
			view.setMainDispute(rs.getString("MAIN_DISPUTE"));
			view.setPunishmentDeal(rs.getInt("PUNISHMENT_DEAL"));
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		return view;
	}
	
	public ISQLMapper getSQLMapper() {
		
		return this;
	}

	public String getTaxpayerNum() {
		return taxpayerNum;
	}

	public void setTaxpayerNum(String taxpayerNum) {
		this.taxpayerNum = taxpayerNum;
	}

	public String getInvolvedEnt() {
		return involvedEnt;
	}

	public void setInvolvedEnt(String involvedEnt) {
		this.involvedEnt = involvedEnt;
	}

	public String getCaseNum() {
		return caseNum;
	}

	public void setCaseNum(String caseNum) {
		this.caseNum = caseNum;
	}

	public String getRecBeginDate() {
		return recBeginDate;
	}

	public void setRecBeginDate(String recBeginDate) {
		this.recBeginDate = recBeginDate;
	}

	public String getRecEndDate() {
		return recEndDate;
	}

	public void setRecEndDate(String recEndDate) {
		this.recEndDate = recEndDate;
	}

	public String getCaseFrom() {
		return caseFrom;
	}

	public void setCaseFrom(String caseFrom) {
		this.caseFrom = caseFrom;
	}

	public String getIllegalType() {
		return illegalType;
	}

	public void setIllegalType(String illegalType) {
		this.illegalType = illegalType;
	}

	public String getInspectDept() {
		return inspectDept;
	}

	public void setInspectDept(String inspectDept) {
		this.inspectDept = inspectDept;
	}

	public String getCollectDept() {
		return collectDept;
	}

	public void setCollectDept(String collectDept) {
		this.collectDept = collectDept;
	}

	public String getCurOrgId() {
		return curOrgId;
	}

	public void setCurOrgId(String curOrgId) {
		this.curOrgId = curOrgId;
	}

	public String getCurPersonId() {
		return curPersonId;
	}

	public void setCurPersonId(String curPersonId) {
		this.curPersonId = curPersonId;
	}

	public String getCurOrgLevel() {
		return curOrgLevel;
	}

	public void setCurOrgLevel(String curOrgLevel) {
		this.curOrgLevel = curOrgLevel;
	}
	public String getCurOrgType() {
		return curOrgType;
	}
	public void setCurOrgType(String curOrgType) {
		this.curOrgType = curOrgType;
	}


}

